IF exists(SELECT * FROM dbo.sysobjects WHERE name='cijfervergelijking' AND xtype='V') 
BEGIN
	DROP VIEW cijfervergelijking
END
GO

CREATE view cijfervergelijking
as
select l1.vakcode,l1.richting,count(l1.TOT4) as populatie,round(1.0*sum(l1.TOT4)/count(l1.TOT4),0) as C05, 
			round(1.0*sum(l2.TOT4)/count(l2.TOT4),0) as C04
from(
	select inschrijvingsNr,vakcode,klas,TOT4,
	 case when klas like '4HW%' then 'Humane wetenschappen'
		when klas like '4La' then 'Latijn 4 uur wiskunde'
		when klas like '4L%' and not klas = '4La' then  'Latijn 5 uur wiskunde' 
		when klas like '4Wa' then 'Wetenschappen 4 uur wiskunde'
		when klas like '4W%' and not klas = '4Wa' then  'Wetenschappen 5 uur wiskunde'
		when klas like '4Ea' then 'Economie 4 uur wiskunde'
		when klas like '4E%' and not klas = '4Ea' then  'Economie 5 uur wiskunde'
		when klas like '4G%' then  'Grieks'
	 else 'onbekend' end as richting
	from loopbaan where jaar = '2005' and klas like '4%' and TOT4 >= 0
     ) as l1
left join(
  	select inschrijvingsNr,vakcode,klas,TOT4 
	from loopbaan where jaar = '2004' and klas like '3%' and TOT4 >= 0
	) as l2
on l1.inschrijvingsNr = l2.inschrijvingsNr and l1.vakcode=l2.vakcode
where not l2.inschrijvingsNr is null
group by richting,l1.vakcode
union
select distinct l1.vakcode,'Vierde jaar ',count(l1.TOT4),round(1.0*sum(l1.TOT4)/count(l1.TOT4),0), 
			round(1.0*sum(l2.TOT4)/count(l2.TOT4),0)
from(
	select inschrijvingsNr,vakcode,TOT4
	from loopbaan where jaar = '2005' and klas like '4%' and TOT4 >=0
     ) as l1
left join(
  	select inschrijvingsNr,vakcode,TOT4 
	from loopbaan where jaar = '2004' and klas like '3%' and TOT4 >=0
	) as l2
on l1.inschrijvingsNr = l2.inschrijvingsNr and l1.vakcode=l2.vakcode
where not l2.inschrijvingsNr is null
group by l1.vakcode